import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: jzj
 * Date: 2024-02-20
 * Time: 16:41
 */
class inquire {
    // 3. 构造 SQL 语句~
    public void select(DataSource dataSource) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要查询的学号: ");
        System.out.println("-1为全部查询");
        int studentId = scanner.nextInt();

        // 2. 和数据库服务器建立连接~~
        Connection connection = dataSource.getConnection();

        // 3. 构造 SQL 语句~
        String sql = "select * from student where id = ?";
        if (studentId == -1) {
            sql = sql.substring(0, 21);
        }
        PreparedStatement statement = connection.prepareStatement(sql);
        if (studentId != -1) {
            statement.setInt(1, studentId);
        }

        // 4. 执行 SQL 语句~. 返回值就是 "这次操作影响到几行"
        // 执行查询操作, 要使用 executeQuery. 返回值是一个 ResultSet 类型的对象. 表示了一个 "表格"
        ResultSet resultSet = statement.executeQuery();
        // 遍历结果集合
        while (resultSet.next()) {
            // 获取到这一行的 学号 列
            int id = resultSet.getInt("id");
            // 获取到这一行的 姓名 列
            String name = resultSet.getString("name");
            System.out.println("id: " + id + ", name: " + name);
        }

        // 5. 释放必要的资源. 关闭连接.
        resultSet.close();
        statement.close();
        connection.close();
    }
}

class edit {
    // 3. 构造 SQL 语句~
    public void update(DataSource dataSource) throws SQLException {
        // 让用户通过控制台来输入学号和姓名.
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要修改的学号: ");
        int id = scanner.nextInt();
        System.out.println("请输入修改后的姓名: ");
        String name = scanner.next();

        // 2. 和数据库服务器建立连接~~
        Connection connection = dataSource.getConnection();


        // 3. 构造 SQL 语句~
        String sql = "update student set name = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, name);
        statement.setInt(2, id);

        // 4. 执行 SQL 语句~. 返回值就是 "这次操作影响到几行"
        int n = statement.executeUpdate();
        System.out.println("n = " + n);

        // 5. 释放必要的资源. 关闭连接.
        statement.close();
        connection.close();


    }

}

class delete {
    // 3. 构造 SQL 语句~
    public void delete_start(DataSource dataSource) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除的 id: ");
        int id = scanner.nextInt();

        // 2. 和数据库服务器建立连接~~
        Connection connection = dataSource.getConnection();


        // 3. 构造 SQL 语句~
        String sql = "delete from student where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);


        // 4. 执行 SQL 语句~. 返回值就是 "这次操作影响到几行"
        int n = statement.executeUpdate();
        System.out.println("n = " + n);

        // 5. 释放必要的资源. 关闭连接.
        statement.close();
        connection.close();


    }

}

class insert {
    // 3. 构造 SQL 语句~
    public void add(DataSource dataSource) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学号: ");
        int id = scanner.nextInt();
        System.out.println("请输入姓名: ");
        String name = scanner.next();

        // 2. 和数据库服务器建立连接~~
        Connection connection = dataSource.getConnection();

        // 3. 构造 SQL 语句~
        String sql = "insert into student values(?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        statement.setString(2, name);

        // 4. 执行 SQL 语句~. 返回值就是 "这次操作影响到几行"
        int n = statement.executeUpdate();
        System.out.println("n = " + n);

        // 5. 释放必要的资源. 关闭连接.
        statement.close();
        connection.close();
    }
}

public class test4 {
    public static void print() {
        System.out.println("1. 插入\n2. 删除\n3. 修改\n4. 查询\n0. 退出");
    }

    public static void main(String[] args) throws SQLException {
        // 假定数据库中有一个 student 表 (id, name)
        System.out.println("欢迎使用 \" 数据库 \"");
        // 1. 创建 "数据源"
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/tmpdata?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("12345");


        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入");
        print();
        int choice = scanner.nextInt();
        while (choice != 0) {
            switch (choice) {
                case 1:
                    insert insert = new insert();
                    insert.add(dataSource);
                    break;
                case 2:
                    delete delete = new delete();
                    delete.delete_start(dataSource);
                    break;
                case 3:
                    edit edit = new edit();
                    edit.update(dataSource);
                    break;
                case 4:
                    inquire inquire = new inquire();
                    inquire.select(dataSource);
                    break;
                default:
                    System.out.println("无效数字");
            }
            System.out.println("请重新输入");
            print();
            choice = scanner.nextInt();
        }
        if (choice == 0) {
            System.out.println("退出成功");
        }
    }
}
